package com.tjsinfo.tjsoa.system.bo;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.hibernate.transaction.JOnASTransactionManagerLookup;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.springframework.jdbc.core.RowMapper;

import com.tjsinfo.tjsoa.Dao.ISpringDao;
import com.tjsinfo.tjsoa.system.vo.TSystemAllgroup;
import com.tjsinfo.tjsoa.system.vo.TSystemDepartment;
import com.tjsinfo.tjsoa.system.vo.TSystemPrivategroup;
import com.tjsinfo.tjsoa.system.vo.TSystemUser;



public class TOATreeBO implements IOATreeBO {
	private ISpringDao oaSpringDao;



	public ISpringDao getOaSpringDao() {
		return oaSpringDao;
	}

	public void setOaSpringDao(ISpringDao oaSpringDao) {
		this.oaSpringDao = oaSpringDao;
	}

	/**
	 * 查询部门树
	 * @return JSONArray集合
	 */
	public JSONArray selectSystemDepartment(){
		List<TSystemDepartment> systemDepartmentList  = new ArrayList<TSystemDepartment>();
		String sql = "SELECT id,deptName,(SELECT COUNT(*) FROM t_system_userDepartment WHERE departmentId = id) AS usercount FROM t_system_department WHERE parentID = 0  ORDER BY orderNo DESC";
		JSONArray array = new JSONArray();
//		if (dataBaseId == 1) {
			systemDepartmentList =	oaSpringDao.querySQL(sql, new Object[]{}, new RowMapper(){

				public Object mapRow(ResultSet arg0, int arg1)
						throws SQLException {
					// TODO Auto-generated method stub
					TSystemDepartment systemDepartment = new TSystemDepartment();
					systemDepartment.setDeptName(arg0.getString("deptName"));
					systemDepartment.setId(arg0.getString("id"));
					systemDepartment.setUserCount(arg0.getInt("usercount"));
					return systemDepartment;
				}
				
			});
		
		for (Iterator iterator = systemDepartmentList.iterator(); iterator
				.hasNext();) {
			TSystemDepartment systemDepartment = (TSystemDepartment) iterator.next();
			JSONObject systemDepartmentObject = new JSONObject();
			try {
				
				systemDepartmentObject.put("text", systemDepartment.getDeptName());
				systemDepartmentObject.put("leaf", false);
				systemDepartmentObject.put("checked", false);
				systemDepartmentObject.put("isdepartment", true);
				JSONArray departmentSubArray =   selectSystemDepartmentSub(systemDepartment.getId()+'%');
				JSONArray userinfoArray = selectSystemPeople(systemDepartment.getId());
				if(departmentSubArray.length() != 0){
					if(userinfoArray.length() != 0){
						departmentSubArray.put(userinfoArray);
					}
					
					systemDepartmentObject.put("children", departmentSubArray);
				}else{
					if(userinfoArray.length() != 0){
						systemDepartmentObject.put("children", userinfoArray);
					}
					
				}
				
				array.put(systemDepartmentObject);
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
		}
		return array;
	}
	
	/**
	 * 查询部门树

	 * @return JSONArray集合
	 */
	public JSONArray selectSystemDepartmentSub(String id){
		List<TSystemDepartment> systemDepartmentList  = new ArrayList<TSystemDepartment>();
		StringBuffer sql = new StringBuffer("SELECT * ,(SELECT COUNT(*) FROM t_system_userDepartment WHERE departmentId = id) AS usercount FROM dbo.t_system_department  WHERE id LIKE  ?  AND LEN(id)> ? ");

		JSONArray array = new JSONArray();
//		if (dataBaseId == 1) {
			systemDepartmentList =	oaSpringDao.querySQL(sql.toString(), new Object[]{id,2}, new RowMapper(){

				public Object mapRow(ResultSet arg0, int arg1)
						throws SQLException {
					// TODO Auto-generated method stub
					TSystemDepartment systemDepartment = new TSystemDepartment();
					systemDepartment.setDeptName(arg0.getString("deptName"));
					systemDepartment.setId(arg0.getString("id"));
					systemDepartment.setUserCount(arg0.getInt("usercount"));
					return systemDepartment;
				}
				
			});
		
		for (Iterator iterator = systemDepartmentList.iterator(); iterator
				.hasNext();) {
			TSystemDepartment systemDepartment = (TSystemDepartment) iterator.next();
			JSONObject systemDepartmentObject = new JSONObject();
			try {
				
				systemDepartmentObject.put("text", systemDepartment.getDeptName());
				systemDepartmentObject.put("leaf", false);
				systemDepartmentObject.put("checked", false);
				systemDepartmentObject.put("isdepartment", true);
				JSONArray userinfoArray = selectSystemPeople(systemDepartment.getId());	
				if(userinfoArray.length() !=0 ){
					systemDepartmentObject.put("children", userinfoArray);
				}
				
				array.put(systemDepartmentObject);
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
		}
		return array;
	}
	/**
	 * 根据数据库ID查询部门树
	 * @param dataBaseId 数据库ID
	 * @return JSONArray集合
	 */
	public JSONArray selectSystemDepartmentRadio(){
		List<TSystemDepartment> systemDepartmentList  = new ArrayList<TSystemDepartment>();
		String sql = "SELECT id,deptName,(SELECT COUNT(*) FROM t_system_userDepartment WHERE departmentId = id) AS usercount FROM t_system_department WHERE parentID = 0  ORDER BY orderNo DESC";
		JSONArray array = new JSONArray();
			systemDepartmentList =	oaSpringDao.querySQL(sql, new Object[]{}, new RowMapper(){

				public Object mapRow(ResultSet arg0, int arg1)
						throws SQLException {
					// TODO Auto-generated method stub
					TSystemDepartment systemDepartment = new TSystemDepartment();
					systemDepartment.setDeptName(arg0.getString("deptName"));
					systemDepartment.setId(arg0.getString("id"));
					systemDepartment.setUserCount(arg0.getInt("usercount"));
					return systemDepartment;
				}
				
			});
		
		for (Iterator iterator = systemDepartmentList.iterator(); iterator
				.hasNext();) {
			TSystemDepartment systemDepartment = (TSystemDepartment) iterator.next();
			JSONObject systemDepartmentObject = new JSONObject();
			try {
				systemDepartmentObject.put("text", systemDepartment.getDeptName());
				systemDepartmentObject.put("leaf", false);
				systemDepartmentObject.put("id", systemDepartment.getId());			
				systemDepartmentObject.put("isdepartment", true);
				JSONArray departmentSubArray =   selectSystemDepartmentSubRadio(systemDepartment.getId()+'%');
				JSONArray userinfoArray = selectSystemPeopleRadio(systemDepartment.getId());
				if(departmentSubArray.length() != 0){
					if(userinfoArray.length() != 0){
						departmentSubArray.put(userinfoArray);
					}
					
					systemDepartmentObject.put("children", departmentSubArray);
				}else{
					if(userinfoArray.length() != 0){
						systemDepartmentObject.put("children", userinfoArray);
					}
					
				}
				
				if(systemDepartmentObject.get("children") != null){
					array.put(systemDepartmentObject);
				}
				
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
		}
		return array;
	}
	
	/**
	 * 根据数据库ID查询部门树

	 * @param dataBaseId 数据库ID
	 * @return JSONArray集合
	 */
	public JSONArray selectSystemDepartmentSubRadio(String id){
		List<TSystemDepartment> systemDepartmentList  = new ArrayList<TSystemDepartment>();
		StringBuffer sql = new StringBuffer("SELECT * ,(SELECT COUNT(*) FROM t_system_userDepartment WHERE departmentId = id) AS usercount FROM dbo.t_system_department  WHERE id LIKE  ?  AND LEN(id)> ? ");

		JSONArray array = new JSONArray();

			systemDepartmentList =	oaSpringDao.querySQL(sql.toString(), new Object[]{id,2}, new RowMapper(){

				public Object mapRow(ResultSet arg0, int arg1)
						throws SQLException {
					// TODO Auto-generated method stub
					TSystemDepartment systemDepartment = new TSystemDepartment();
					systemDepartment.setDeptName(arg0.getString("deptName"));
					systemDepartment.setId(arg0.getString("id"));
					systemDepartment.setUserCount(arg0.getInt("usercount"));
					return systemDepartment;
				}
				
			});

		
		for (Iterator iterator = systemDepartmentList.iterator(); iterator
				.hasNext();) {
			TSystemDepartment systemDepartment = (TSystemDepartment) iterator.next();
			JSONObject systemDepartmentObject = new JSONObject();
			try {
				
				systemDepartmentObject.put("text", systemDepartment.getDeptName());
				systemDepartmentObject.put("leaf", false);
				systemDepartmentObject.put("checked", false);
				systemDepartmentObject.put("isdepartment", true);
				JSONArray userinfoArray = selectSystemPeopleRadio( systemDepartment.getId());	
				if(userinfoArray.length() !=0 ){
					systemDepartmentObject.put("children", userinfoArray);
				}
				
				array.put(systemDepartmentObject);
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
		}
		return array;
	}
	
	/**
	 * 查询用户
	 * @param departmentId 部门ID
	 * @return JSONArray集合
	 */
	public JSONArray selectSystemPeople(String departmentId){
		List<TSystemUser> systemUserInfoList = new ArrayList<TSystemUser>();
		JSONArray array = new JSONArray();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT systemuser.userid,systemuser.uuid,systemuser.userName FROM t_system_user AS systemuser ");
		sql.append("INNER JOIN t_system_userDepartment AS userDepartment ON systemuser.userid = userDepartment.userId ");
		sql.append("INNER JOIN t_system_department  AS department ON department.id = userDepartment.departmentId ");
		sql.append("WHERE department.id = ? ");
			systemUserInfoList =	oaSpringDao.querySQL(sql.toString(), new Object[]{departmentId}, new RowMapper(){

				public Object mapRow(ResultSet arg0, int arg1)
						throws SQLException {
					// TODO Auto-generated method stub
					TSystemUser systemUser = new TSystemUser();
					systemUser.setUserId(arg0.getInt("userid"));
					systemUser.setUserName(arg0.getString("userName"));
					systemUser.setUuid(arg0.getString("uuid"));
					return systemUser; 
				}
				
			});
	
		for(TSystemUser systemUserInfo : systemUserInfoList) {
			JSONObject object = new JSONObject();
			try {
				object.put("text", systemUserInfo.getUserName());
				object.put("id", systemUserInfo.getUserId());
				object.put("checked", false);
				object.put("leaf", true);
				array.put(object);
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		return array;
	}
	
	/**
	 * 根据数据库ID查询用户
	 * @param dataBaseId 数据库ID
	 * @param departmentId 部门ID
	 * @return JSONArray集合
	 */
	public JSONArray selectSystemPeopleRadio(String departmentId){
		List<TSystemUser> systemUserInfoList = new ArrayList<TSystemUser>();
		JSONArray array = new JSONArray();
		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT systemuser.userid,systemuser.uuid,systemuser.userName FROM t_system_user AS systemuser ");
		sql.append("INNER JOIN t_system_userDepartment AS userDepartment ON systemuser.userid = userDepartment.userId ");
		sql.append("INNER JOIN t_system_department  AS department ON department.id = userDepartment.departmentId ");
		sql.append("WHERE department.id = ? ORDER BY systemuser.orderNO ASC ");
			systemUserInfoList =	oaSpringDao.querySQL(sql.toString(), new Object[]{departmentId}, new RowMapper(){

				public Object mapRow(ResultSet arg0, int arg1)
						throws SQLException {
					// TODO Auto-generated method stub
					TSystemUser systemUserInfo = new TSystemUser();
					systemUserInfo.setUserId(arg0.getInt("userid"));
					systemUserInfo.setUserName(arg0.getString("userName"));
					systemUserInfo.setUuid(arg0.getString("uuid"));
					return systemUserInfo; 
				}
				
			});
	
		for(TSystemUser systemUserInfo : systemUserInfoList) {
			JSONObject object = new JSONObject();
			try {
				object.put("text", systemUserInfo.getUserName());
				object.put("id", systemUserInfo.getUserId());
				object.put("leaf", true);
				array.put(object);
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		return array;
	}
	
	
	
	/**
	 * 根据数据库ID查询公共组
	 * @param dataBaseId 数据库ID
	 * @return JSONArray集合
	 */
	public JSONArray selectSystemAllGroup(){
		List<TSystemAllgroup> systemAllGroupList = new ArrayList<TSystemAllgroup>();
		JSONArray array = new JSONArray();
		StringBuffer sql = new StringBuffer("SELECT id, groupName FROM t_system_allgroup WHERE state = 0 ORDER BY orderNo ASC ");
			systemAllGroupList =	oaSpringDao.querySQL(sql.toString(), new Object[]{}, new RowMapper(){

				public Object mapRow(ResultSet arg0, int arg1)
						throws SQLException {
					// TODO Auto-generated method stub
					TSystemAllgroup systemAllGroup = new TSystemAllgroup();					
					systemAllGroup.setGroupName(arg0.getString("groupName"));
					systemAllGroup.setId(arg0.getInt("id"));
					return systemAllGroup;
				}
				
			});
	
		for (TSystemAllgroup systemAllGroup : systemAllGroupList) {
			JSONObject object = new JSONObject();
			try {
				//object.put("id", systemAllGroup.getId());
				//object.put("id", getMD5(systemAllGroup.getId().toString().getBytes()));
				object.put("text", systemAllGroup.getGroupName());
				object.put("leaf", false);
				object.put("isdepartment", true);
				object.put("checked", false);
				JSONArray userinfoArray = selectSystemAllGroupUser( systemAllGroup.getId());
				object.put("children", userinfoArray);
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			array.put(object);			
		}
		return array;
	}
	
	/**
	 * 根据数据库ID查询公共组
	 * @param dataBaseId 数据库ID
	 * @param groupId    组ID
	 * @return JSONArray集合
	 */
	public JSONArray selectSystemAllGroupUser(Integer groupId){
		List<TSystemUser> systemUserInfoList = new ArrayList<TSystemUser>();
		JSONArray array = new JSONArray();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT systemuser.userId,systemuser.userName  FROM t_system_user AS systemuser  ");
		sql.append("INNER JOIN t_system_userallgroup AS userallgroup ON userallgroup.userid = systemuser.userId ");
		sql.append("INNER JOIN t_system_allgroup AS allgroup ON allgroup.id = userallgroup.allgroupid  ");
		sql.append("WHERE allgroup.id = ? ORDER BY systemuser.orderNo ASC ");
			systemUserInfoList =	oaSpringDao.querySQL(sql.toString(), new Object[]{groupId}, new RowMapper(){

				public Object mapRow(ResultSet arg0, int arg1)
						throws SQLException {
					// TODO Auto-generated method stub
					TSystemUser systemUserInfo = new TSystemUser();
					systemUserInfo.setUserId(arg0.getInt("userid"));
					systemUserInfo.setUserName(arg0.getString("userName"));					
					return systemUserInfo; 
				}
				
			});
	
		for(TSystemUser systemUserInfo : systemUserInfoList) {
			JSONObject object = new JSONObject();
			try {
				object.put("text", systemUserInfo.getUserName());
				object.put("id", systemUserInfo.getUserId());
				object.put("leaf", true);
				object.put("isdepartment", false);
				object.put("checked", false);
				array.put(object);
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		return array;
	}
	
	/**
	 * 根据数据库ID查询自定义组
	 * @param dataBaseId 数据库ID
	 * @param sessionId  会话ID
	 * @return JSONArray集合
	 */
	public JSONArray selectSystemPrivateGroup(Integer sessionId){
		List<TSystemPrivategroup> systemPrivateGroupList = new ArrayList<TSystemPrivategroup>();
		JSONArray array = new JSONArray();
		StringBuffer sql = new StringBuffer("SELECT id, groupName FROM t_system_privategroup WHERE state = 0  AND  sessionId = ? ORDER BY orderNo ASC ");
			systemPrivateGroupList =	oaSpringDao.querySQL(sql.toString(), new Object[]{sessionId}, new RowMapper(){

				public Object mapRow(ResultSet arg0, int arg1)
						throws SQLException {
					// TODO Auto-generated method stub
					TSystemPrivategroup systemAllGroup = new TSystemPrivategroup();					
					systemAllGroup.setGroupName(arg0.getString("groupName"));
					systemAllGroup.setId(arg0.getInt("id"));
					return systemAllGroup;
				}
				
			});
		
		for (TSystemPrivategroup systemAllGroup : systemPrivateGroupList) {
			JSONObject object = new JSONObject();
			try {
				//object.put("id", systemAllGroup.getId());
				object.put("text", systemAllGroup.getGroupName());
				object.put("leaf", false);
				object.put("isdepartment", true);
				object.put("checked", false);
				JSONArray userinfoArray = selectSystemPrivateGroupUser( systemAllGroup.getId());
				object.put("children", userinfoArray);
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			array.put(object);			
		}
		return array;
	}
	
	/**
	 * 根据数据库ID查询自定义组
	 * @param dataBaseId 数据库ID
	 * @param groupId    组ID
	 * @param sessionId  会话ID
	 * @return JSONArray集合
	 */
	public JSONArray selectSystemPrivateGroupUser(Integer groupId){
		List<TSystemUser> systemUserInfoList = new ArrayList<TSystemUser>();
		JSONArray array = new JSONArray();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT systemuser.userId,systemuser.userName   FROM t_system_user AS systemuser  ");
		sql.append("INNER JOIN t_system_userprivategroup AS userprivategroup ON userprivategroup.userId = systemuser.userId ");
		sql.append("INNER JOIN t_system_privategroup AS privategroup ON privategroup.id = userprivategroup.privategroupid  ");
		sql.append("WHERE privategroup.id = ?   ORDER BY systemuser.orderNo ASC  ");
			systemUserInfoList =	oaSpringDao.querySQL(sql.toString(), new Object[]{groupId}, new RowMapper(){

				public Object mapRow(ResultSet arg0, int arg1)
						throws SQLException {
					// TODO Auto-generated method stub
					TSystemUser systemUserInfo = new TSystemUser();
					systemUserInfo.setUserId(arg0.getInt("userid"));
					systemUserInfo.setUserName(arg0.getString("userName"));					
					return systemUserInfo; 
				}
				
			});
	
		for(TSystemUser systemUserInfo : systemUserInfoList) {
			JSONObject object = new JSONObject();
			try {
				object.put("text", systemUserInfo.getUserName());
				object.put("id", systemUserInfo.getUserId());
				object.put("leaf", true);
				object.put("isdepartment", false);
				object.put("checked", false);
				array.put(object);
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		return array;
	}
	
	/**
	 *根据用户名查询用户 
	 */
	public JSONArray seachUserInfo(Integer dataBaseId,String username){
		List<TSystemUser> systemUserInfoList = new ArrayList<TSystemUser>();
		JSONArray array = new JSONArray();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM t_system_user WHERE userName LIKE ? ORDER BY orderNo ASC");
			systemUserInfoList =	oaSpringDao.querySQL(sql.toString(), new Object[]{"%"+username+"%"}, new RowMapper(){

				public Object mapRow(ResultSet arg0, int arg1)
						throws SQLException {
					// TODO Auto-generated method stub
					TSystemUser systemUserInfo = new TSystemUser();
					systemUserInfo.setUserId(arg0.getInt("userid"));
					systemUserInfo.setUserName(arg0.getString("userName"));					
					return systemUserInfo; 
				}
				
			});
	
		for(TSystemUser systemUserInfo : systemUserInfoList) {
			JSONObject object = new JSONObject();
			try {
				object.put("userName", systemUserInfo.getUserName());
				object.put("userid", systemUserInfo.getUserId());
				object.put("dataBaseId", dataBaseId);
				array.put(object);
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		return array;
	}

	public  String getMD5(byte[] source) {
		  String s = null;
		  char hexDigits[] = {       // 用来将字节转换成 16 进制表示的字符
		     '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'a', 'b', 'c', 'd',  'e', 'f'}; 
		   try
		   {
		    java.security.MessageDigest md = java.security.MessageDigest.getInstance( "MD5" );
		    md.update( source );
		    byte tmp[] = md.digest();          // MD5 的计算结果是一个 128 位的长整数，
		                                                // 用字节表示就是 16 个字节
		    char str[] = new char[16 * 2];   // 每个字节用 16 进制表示的话，使用两个字符，
		                                                 // 所以表示成 16 进制需要 32 个字符
		    int k = 0;                                // 表示转换结果中对应的字符位置
		    for (int i = 0; i < 16; i++) {          // 从第一个字节开始，对 MD5 的每一个字节
		                                                 // 转换成 16 进制字符的转换
		     byte byte0 = tmp[i];                 // 取第 i 个字节
		     str[k++] = hexDigits[byte0 >>> 4 & 0xf];  // 取字节中高 4 位的数字转换, 
		                                                             // >>> 为逻辑右移，将符号位一起右移
		     str[k++] = hexDigits[byte0 & 0xf];            // 取字节中低 4 位的数字转换
		    } 
		    s = new String(str);                                 // 换后的结果转换为字符串

		   }catch( Exception e )
		   {
		    e.printStackTrace();
		   }
		   return s;
		 }
}
